<?php
/**
 * Created by PhpStorm.
 * User: Yieong
 * Date: 14/11/19
 * Time: 下午8:12
 */

class ModChannel extends Model {

    protected $table = 'channel';

    // 表名
    public function tableName(){
        return $this->table;
    }

    public static function model($className=__CLASS__){
        return parent::model($className);
    }

    public function getDbConnection(){
        return Yii::app()->dbOss;
    }
    
    public function page($params){
    	$table = $this->table;
    	
        $where  = ' WHERE game=:game ';
        $args   = array(
            ':game'     => $params['g']
        );

        if(!empty($params['channel_name'])){
            $where .= ' AND channel_name LIKE :name ';
            $args[':name'] = '%'.$params['channel_name'].'%';
        }
        
        if(!empty($params['special_id'])){
        	$where .= ' AND special_id = :special_id ';
        	$args[':special_id'] = $params['special_id'];
        }
    
    	//$c_sql = "select count(1) as cnt from {$table} as c left JOIN special_channel as sc on sc.channel_id = c.id {$where}";
    	$c_sql = "select count(1) as cnt from {$table} as c {$where}";
    	$c_result = $this->getDbConnection()->query($c_sql, $args);
    	$count = isset($c_result[0]['cnt']) ? $c_result[0]['cnt'] : 0;
    
    	$order  = self::getOrder($params['order']);
    	$limit  = PubFunc::getLimit($params['length'], $params['page']);
    	$fields = "c.*";
    	$sql = "SELECT {$fields} FROM {$table} as c {$where} {$order} {$limit}";
//     	$fields = "c.*,sc.special_id";
//     	$sql = "SELECT {$fields} FROM {$table} as c left JOIN special_channel as sc on sc.channel_id = c.id {$where} {$order} {$limit}";
    	$data = $this->getDbConnection()->query($sql, $args);
    	$result = array('count'=>$count,'data'=>$data);
    	return $result;
    }
    
    public static function getOrder($order){
    	if(PubFunc::checkEmpty($order, array(0, 1))) return '';
    	if(!in_array($order[1], array('asc', 'desc'))){
    		$sort = 'asc';
    	}else{
    		$sort = $order[1];
    	}
    	return " ORDER BY {$order[0]} {$sort} ";
    }

    public function select($params){
        $where  = ' WHERE game=:game ';
        $args   = array(
            ':game'     => $params['g']
        );

        if(!empty($params['channel_name'])){
            $where .= ' AND channel_name LIKE :name ';
            $args[':name'] = '%'.$params['channel_name'].'%';
        }

        $sql = "SELECT * FROM {$this->table} {$where} ORDER BY create_time DESC";

        $result = $this->getDbConnection()->getAll($sql, $args);
        return $result;
    }
    
    public function selectAll(){
    	$sql = "SELECT * FROM {$this->table} ORDER BY create_time DESC";
    
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    //根据渠道tab获取服务器
    public function getServer($os,$g){
    	$sql = "select c.channel_name,sc.server_id,s.server_name,s.develop_id,s.id,s.server_num from channel as c 
    			INNER JOIN server_channel as sc ON c.id = sc.channel_id INNER JOIN server as s ON sc.server_id = s.id WHERE c.tab = '{$os}' and c.game = '{$g}'
    			and s.game = '{$g}' order by s.server_num desc ";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    //根据渠道主键id获取服务器
    public function getserverforchannelId($id,$g){
    	$sql = "select c.channel_name,sc.server_id,s.server_name,s.develop_id,s.id from channel as c
    	INNER JOIN server_channel as sc ON c.id = sc.channel_id INNER JOIN server as s ON sc.server_id = s.id WHERE c.id = '{$id}' and c.game = '{$g}'
    	and s.game = '{$g}'";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    public function repeat($id,$field,$value){
    	$sql = "select count(*) from {$this->table} where id = {$id} and {$field} = {$value} ";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    public function crepeat($id,$field,$val,$g){
    	$sql = "SELECT count(1) as cnt FROM {$this->table} where id != {$id} and {$field} = '{$val}' and game = '{$g}'";
    	$c_result = $this->getDbConnection()->getAll($sql, $args);
    	$count = isset($c_result[0]['cnt']) ? $c_result[0]['cnt'] : 0;
    	return $count;
    }
    
    /**
     * 根据渠道 ids 查询
     * @param unknown $ids
     * @return unknown
     */
    public function getChannelForIds($ids){
    	$sql = "select id,channel_name,tab FROM {$this->table} where id in ({$ids})";  	 
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    public function getTabCount($tabs){
    	$sql = "select id FROM {$this->table} where tab in ({$tabs})";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    /**
     * 根据用户具备的渠道得到专服
     * @param unknown $tabs
     * @return unknown
     */
    public function getSpecialForChannel($tabs){
    	$sql = "SELECT c.id,c.tab,sc.special_id,ss.name from channel as c INNER JOIN special_channel as sc ON sc.channel_id = c.id INNER JOIN special_server as ss ON ss.id = sc.special_id WHERE tab in ({$tabs})";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    /**
     * 根据专服获取渠道
     * @param unknown $special_id
     * @return unknown
     */
    public function getChannelForSpecial($special_id){
    	$sql = "SELECT special_id,channel_id,c.channel_name,c.tab from special_channel as ac INNER JOIN channel as c ON c.id = ac.channel_id where special_id = {$special_id}";
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    /**
     * 用于选择渠道
     * @param unknown $channel_ids
     */
    public function searchChannel($gane,$channel_ids){
    	if($channel_ids == ''){
    		$sql = "SELECT id,channel_name,tab FROM channel WHERE game = '{$gane}' ";
    	}else{
    		$sql = "SELECT id,channel_name,tab FROM channel WHERE game = '{$gane}' and id not in ({$channel_ids}) ";
    	}
    	
    	$result = $this->getDbConnection()->getAll($sql, $args);
    	return $result;
    }
    
    
    
} 